package reports;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.sql.*;
import java.io.IOException;
import java.io.FileOutputStream;
import java.util.*;
import java.text.SimpleDateFormat;

import utilities.DateUtils;

import javax.mail.*;
import javax.mail.internet.*;
import javax.activation.DataSource;
import javax.activation.FileDataSource;
import javax.activation.DataHandler;

/**
 * Created by IntelliJ IDEA.
 * User: raj.chhabra
 * Date: Nov 24, 2010
 * Time: 9:24:15 AM
 * To change this template use File | Settings | File Templates.
 */
public class InventoryReport extends ReportUtilities implements Report {

    private static final String[] titles = {
            "Vendor", "VSN","Item Code","Description","Store Code",
            "Location Code", "Quantity","Fifo Cost","Replacement Cost","Retail Price"};

    private static final String reportTitle = "Inventory Report";            

    private Map<String, Map> locationMap;

    public static void main(String[] args) throws Exception, SQLException, IOException {

        InventoryReport ir = new  InventoryReport();
        Connection conn = null; // = ir.getDBConnection(args[0]);
        /*ir.fileName = args[1];
        ir.useMultipleSheets = new Boolean(args[2]);
        */
        ir.email = false;

        String param = "";

        for (int i = 0; i<args.length; i++){
            param = args[i];
            String[] params = param.split("=");
            if (params[0].equals("DB")){
                conn = ir.getDBConnection(params[1]);
            } else if (params[0].equals("FILENAME")) {
                ir.fileName = params[1];
            } else if (params[0].equals("MULTIPLESHEETS")) {
                ir.useMultipleSheets = new Boolean(params[1]);
            } else if (params[0].equals("EMAIL")) {
                ir.email = new Boolean(params[1]);
            } else {
                System.out.println(params[0]+" is an invalid parameter.");
                System.exit(0);
            }
        }
        //Gets FIFO inventory report
        String invQuery = "select distinct ve_cd,vsn,i.itm_cd,i.des,FIF.STORE_CD,FIF.LOC_CD,fif.qty,FIFO.CST,I.REPL_CST,I.RET_PRC " +
                "from itm i, itm_fifl fif, itm_fifo fifo, " +
                "(select min(itm_fifo.rcv_dt) as rcvdt, itm_cd from itm_fifo group by itm_cd) t1 " +
                "where  i.itm_cd=FIF.ITM_CD " +
                "and i.itm_cd = fifo.itm_cd " +
                "and FIFO.ITM_CD = t1.itm_cd " +
                "and fifo.rcv_dt=t1.rcvdt " +
                "order by store_cd,loc_cd,itm_cd";

        //Gets inventory items in transit
        String invIstQuery = "select t1.*, itm.ve_cd,itm.vsn,itm.itm_cd,itm.des,itm.repl_cst,ITM.RET_PRC from itm, " +
                "(select IST_LN.OLD_STORE_CD as store_cd, ist_ln.old_loc_cd as loc_cd, ist_ln.itm_cd,ist_ln.fifo_cst as cst, sum(ist_ln.qty) as qty from ist, ist_ln " +
                "where ist.stat_cd = 'O' " +
                "and ist.ist_seq_num = ist_ln.ist_seq_num " +
                "and IST_LN.void_flag='N' " +
                "group by ist_ln.old_store_cd, ist_ln.old_loc_cd, ist_ln.itm_cd, ist_ln.fifo_cst " +
                "order by ist_ln.old_store_cd, ist_ln.old_loc_cd, ist_ln.itm_cd) t1 " +
                "where t1.itm_cd = itm.itm_cd";

        //Gets inventory items that are attached to SO_LN
        String invSolnQuery ="select t1.*, itm.ve_cd,itm.vsn,itm.des,itm.repl_cst,ITM.RET_PRC from itm, " +
                "(select so_ln.store_cd,so_ln.loc_cd,so_ln.itm_cd, so_ln.fifo_cst as cst, sum(so_ln.qty) as qty from so, so_ln " +
                "where so.stat_cd = 'O' " +
                "and so_ln.store_cd is not null " +
                "and so_ln.picked='F' " +
                "and SO_LN.VOID_FLAG='N' " +
                "and so.del_doc_num=so_ln.del_doc_num " +
                "group by SO_LN.STORE_CD,SO_LN.LOC_CD,so_ln.itm_cd,so_ln.fifo_cst) t1 " +
                "where t1.itm_cd = itm.itm_cd " +
                "order by t1.STORE_CD,t1.loc_cd,t1.itm_cd";

        //Gets NAS (not available for sale) inventory items
        String invNASQuery = "select nas.itm_cd, nas.cst,nas.store_cd,nas.loc_cd,nas.qty,itm.ve_cd,itm.vsn,itm.des,itm.repl_cst,ITM.RET_PRC from itm, nas " +
                "where nas.itm_cd = itm.itm_cd " +
                "order by store_cd, loc_cd, nas.itm_cd";

        /*String invQuery = "select  distinct ve_cd,vsn,i.itm_cd,i.des,FIF.STORE_CD,FIF.LOC_CD,fif.qty,FIFO.CST,I.REPL_CST,I.RET_PRC\n" +
                "from itm i, itm_fifl fif, itm_fifo fifo,\n" +
                "(select min(itm_fifo.rcv_dt) as rcvdt, itm_cd from itm_fifo group by itm_cd) t1\n" +
                "where  i.itm_cd=FIF.ITM_CD\n" +
                "and i.itm_cd = fifo.itm_cd\n" +
                "and FIFO.ITM_CD = t1.itm_cd\n" +
                "and fifo.rcv_dt=t1.rcvdt\n" +
                "order by store_cd,loc_cd,itm_cd";*/

        /*String invQuery = "select distinct ve_cd,vsn,i.itm_cd,i.des,FIF.STORE_CD,FIF.LOC_CD,fif.qty,FIFO.CST,I.REPL_CST,I.RET_PRC " +
                "from itm i, itm_fifl fif, itm_fifo fifo " +
                "where i.itm_cd=FIF.ITM_CD and i.itm_cd = fifo.itm_cd " +
                "order by store_cd,loc_cd,itm_cd";*/


        Statement stmt = conn.createStatement();
        Statement stmt2 = conn.createStatement();
        Statement stmt3 = conn.createStatement();
        Statement stmt4 = conn.createStatement();

        ResultSet rs = stmt.executeQuery(invQuery);
        ResultSet istRs = stmt2.executeQuery(invIstQuery);
        ResultSet solnRs = stmt3.executeQuery(invSolnQuery);
        ResultSet nasRs = stmt4.executeQuery(invNASQuery);

        System.out.println("calling createMap(rs)");
        ir.createMap(rs);
        System.out.println("calling updateLocationMap(rs) for IST");
        ir.updateLocationMap(istRs);
        System.out.println("calling updateLocationMap(rs) for SOLN");
        ir.updateLocationMap(solnRs);
        System.out.println("calling updateLocationMap(rs) for NAS");
        ir.updateLocationMap(nasRs);
        System.out.println("calling createSpreadsheet()");
        ir.createSpreadsheet();
        
        rs.close();
        istRs.close();
        solnRs.close();
        nasRs.close();

        stmt.close();
        stmt2.close();
        stmt3.close();
        stmt4.close();

        if (ir.email){
            try {
            ir.email(ir.fileName);
            } catch (AddressException e) {
                throw new AddressException(e.getMessage());
            }

        }
    }



    private void updateLocationMap(ResultSet rs) throws SQLException, IOException{
        System.out.println("updating location map");
        String storeCode = "";
        String locationCode ="";
        String key = "";
        String currentStoreCode="";
        String currentLocationCode="";
        int currentQty;
        int newQty;
        Map<String,Item> itemMap = new TreeMap<String,Item>();

        while(rs.next()){
            currentStoreCode = rs.getString("store_cd");
            currentLocationCode = rs.getString("loc_cd");

            if (currentStoreCode == null || currentLocationCode == null)
                continue;
                
            if (!currentStoreCode.equalsIgnoreCase(storeCode) ||
                    (currentStoreCode.equalsIgnoreCase(storeCode) && !currentLocationCode.equalsIgnoreCase(locationCode))){
                if (!itemMap.isEmpty())
                    locationMap.put(storeCode+locationCode,itemMap);
                storeCode = currentStoreCode;
                locationCode = currentLocationCode;
                key=storeCode+locationCode;
                itemMap = locationMap.get(key);
                if (itemMap == null)
                    itemMap = new HashMap<String,Item>();
            }

            Item item;
            item = itemMap.get(rs.getString("itm_cd"));
            if (item == null) {
                item = new Item();
                item.setVendor(rs.getString("ve_cd"));
                item.setVsn(rs.getString("vsn"));
                item.setItemCode(rs.getString("itm_cd"));
                item.setDescription(rs.getString("des"));
                item.setStoreCode(rs.getString("store_cd"));
                item.setLocationCode(rs.getString("loc_cd"));
                item.setQuantity(rs.getInt("qty"));
                item.setFifoCost(rs.getString("cst"));
                item.setReplacementCost(rs.getString("repl_cst"));
                item.setRetailPrice(rs.getString("ret_prc"));
            } else {
                currentQty = item.getQuantity();
                newQty = currentQty + rs.getInt("qty");
                item.setQuantity(newQty);
            }
            itemMap.put(rs.getString("itm_cd"),item);
        }
        System.out.println("finished updating location map");
    }

    private void createMap(ResultSet rs) throws SQLException, IOException{
        System.out.println("Creating Location Map");
        String storeCode = "";
        String locationCode ="";
        String key = "";
        String currentStoreCode="";
        String currentLocationCode="";
        String currentKey="";
        locationMap=new TreeMap<String,Map>();
        Map<String,Item> itemMap = new HashMap<String,Item>();
        while(rs.next()){
            
            currentStoreCode = rs.getString("store_cd");
            currentLocationCode = rs.getString("loc_cd");
            currentKey = currentStoreCode+currentLocationCode;
            
            if (!currentStoreCode.equalsIgnoreCase(storeCode) ||
                    (currentStoreCode.equalsIgnoreCase(storeCode) && !currentLocationCode.equalsIgnoreCase(locationCode))){
                if (!itemMap.isEmpty())
                    locationMap.put(storeCode+locationCode,itemMap);
                storeCode = currentStoreCode;
                locationCode = currentLocationCode;
                key=storeCode+locationCode;
                itemMap = new HashMap<String,Item>();                
            }

            Item item = new Item();
            item.setVendor(rs.getString("ve_cd"));
            item.setVsn(rs.getString("vsn"));
            item.setItemCode(rs.getString("itm_cd"));
            item.setDescription(rs.getString("des"));
            item.setStoreCode(rs.getString("store_cd"));
            item.setLocationCode(rs.getString("loc_cd"));
            item.setQuantity(rs.getInt("qty"));
            item.setFifoCost(rs.getString("cst"));
            item.setReplacementCost(rs.getString("repl_cst"));
            item.setRetailPrice(rs.getString("ret_prc"));

            itemMap.put(rs.getString("itm_cd"),item);           
        }
        //add last one
    if (!itemMap.isEmpty())
        locationMap.put(storeCode+locationCode,itemMap);
            
    System.out.println("Finished creating location map");
    }

    public void createSpreadsheet() throws SQLException, IOException {
        System.out.println("Creating spreadsheet");
        Workbook wb = new HSSFWorkbook();

        Map<String, CellStyle> styles = createStyles(wb);
        Row row = null;
        Cell cell = null;
        int rownum = 2;
        String storeCode = "";
        String locationCode ="";
        String currentStoreCode="";
        String currentLocationCode="";
        boolean sheetCreated=false;
        Sheet sheet = null;

        for (Map.Entry<String, Map> entry : locationMap.entrySet()) {     
            String locationKey = entry.getKey();
            Map<String,Item> itemMap = entry.getValue();

            for (Map.Entry<String,Item> itemEntry:itemMap.entrySet()) {
                int j=0;
                String itemCodeKey = itemEntry.getKey();
                Item item = itemEntry.getValue();

                currentStoreCode = item.getStoreCode();
                currentLocationCode = item.getLocationCode();

                if (useMultipleSheets){
                    if (!currentStoreCode.equalsIgnoreCase(storeCode) ||
                            (currentStoreCode.equalsIgnoreCase(storeCode) && !currentLocationCode.equalsIgnoreCase(locationCode))){
                        sheet = createNewSheet((HSSFWorkbook)wb, currentStoreCode + " " + currentLocationCode, titles, reportTitle, styles);
                        storeCode = currentStoreCode;
                        locationCode = currentLocationCode;
                        rownum = 2;
                    }
                } else if (!sheetCreated){
                    sheet = createNewSheet((HSSFWorkbook)wb, "All Stores", titles, reportTitle, styles);
                    sheetCreated=true;
                }
                //TODO: put cell creation in a loop to reduce the amount of code here
                if (sheet != null)
                    row = sheet.createRow(rownum);
                if (row != null) {
                    cell = row.createCell(j);
                    cell.setCellValue(item.getVendor());
                    cell.setCellStyle(styles.get("cell_normal"));
                    j++;

                    cell = row.createCell(j);
                    cell.setCellValue(item.getVsn());
                    cell.setCellStyle(styles.get("cell_normal"));
                    j++;

                    cell = row.createCell(j);
                    cell.setCellValue(item.getItemCode());
                    cell.setCellStyle(styles.get("cell_normal"));
                    j++;

                    cell = row.createCell(j);
                    cell.setCellValue(item.getDescription());
                    cell.setCellStyle(styles.get("cell_normal"));
                    j++;

                    cell = row.createCell(j);
                    cell.setCellValue(item.getStoreCode());
                    cell.setCellStyle(styles.get("cell_normal"));
                    j++;

                    cell = row.createCell(j);
                    cell.setCellValue(item.getLocationCode());
                    cell.setCellStyle(styles.get("cell_normal"));
                    j++;

                    cell = row.createCell(j);
                    cell.setCellValue(item.getQuantity());
                    cell.setCellStyle(styles.get("cell_normal"));
                    j++;

                    cell = row.createCell(j);
                    cell.setCellValue(item.getFifoCost());
                    cell.setCellStyle(styles.get("cell_normal_money"));
                    j++;

                    cell = row.createCell(j);
                    cell.setCellValue(item.getReplacementCost());
                    cell.setCellStyle(styles.get("cell_normal_money"));
                    j++;

                    cell = row.createCell(j);
                    cell.setCellValue(item.getRetailPrice());
                    cell.setCellStyle(styles.get("cell_normal_money"));
                    
                    rownum++;
                }
            }
        }

        for (int x=0; x<wb.getNumberOfSheets();x++){
            Sheet s = wb.getSheetAt(x);
            for (int k=0; k<12; k++){
                s.autoSizeColumn((short)k);
            }
        }

        // Write the output to a file
        if(wb instanceof XSSFWorkbook) fileName += "x";
        fileName = fileName + DateUtils.now("yyyyMMdd")+".xls";
        FileOutputStream out = new FileOutputStream(fileName);
        wb.write(out);
        out.close();

        System.out.println("Finished creating spreadsheet");
    }

    //TODO: move email functionality to a seperate class so it can send all four reports at once.
    private void email(String fileName) throws AddressException {
        String host = "192.168.100.2";
        Address from = new InternetAddress("rajeev.chhabra@localhost.com");
        Address to = new InternetAddress("rajeev.chhabra@gmail.com");

        Properties props = new Properties();
        props.setProperty("mail.smtp.host",host);
        Session session = Session.getDefaultInstance(props,null);

        try {
            Message message = new MimeMessage(session);
            message.setFrom(from);
            message.addRecipient(Message.RecipientType.TO,to);
            message.setSubject("Inventory Report for "+DateUtils.now("yyyyMMdd"));

            // Create the message part
            BodyPart messageBodyPart = new MimeBodyPart();
            messageBodyPart.setText("Please find attached the Inventory Report for " + DateUtils.now("yyyyMMdd"));
            Multipart multipart = new MimeMultipart();
            multipart.addBodyPart(messageBodyPart);

            // Part two is attachment
            messageBodyPart = new MimeBodyPart();
            DataSource source = new FileDataSource(fileName);
            messageBodyPart.setDataHandler(new DataHandler(source));
            messageBodyPart.setFileName(fileName);
            multipart.addBodyPart(messageBodyPart);
            // Put parts in message
            message.setContent(multipart);
            Transport.send(message);
            System.out.println("Done");

        } catch (MessagingException e) {
            throw new RuntimeException(e);
        }
    }
}
